﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using AcctrueWMS.Foundation.DAL;

namespace AcctruePMS.Bll.Manager
{
     public class ProjectSolutionManager
    {
         /// <summary>
         /// 根据权限获取对应的项目报告问题数据
         /// </summary>
         /// <param name="userNo"></param>
         /// <returns></returns>
         public DataTable GetProjectSolutionByPower(string userNo)
         {
             
             SqlParameter[] pars = { new SqlParameter("@userNo", SqlDbType.NVarChar, 50) };
             pars[0].Value = userNo;
             string sql0 = "select count(*) from userRoleInfo where (roleCode='01' or roleCode='02') and userNo=@userNo;";//查询是否是 总经理或副总经理

             string sql1 = "select industryLineCode as code from industryLinePrincipal where userNo=@userNo group by industryLineCode;";//查询是否是行业线负责人

             string sql2 = "select distinct picNo,wbsCode from departments where picNo=@userNo union select businessNo,wbsCode from departments where businessNo=@userNo;";//查询出部门负责人

             int num = (int)WMSqlExcuter.ExecuteScalar(sql0, CommandType.Text, pars);
             if (num > 0)
             {//总经理 和副总经理的权限
                 string sql = "select qusetionDes,needAttention,advise,projectSolution.upfileGuid,projectSolution.repotGuid,questionState,questionType,userNo,expectSolveTime,CONVERT(VARCHAR(50),projectQuestion.projectGUID) as projectGUID,projectQuestion.cteateNo,createBy,projectManager, lastSales, sales,industryLineCode,departments.departName,project.departWBS,project.projectName,ifAlerm=(case when expectSolveTime<dateadd(d,-1,dateadd(d,1,convert(datetime,Convert(varchar(10),getDate(),120),120)))  and ((questionState<>'05' and questionState<>'04') or questionState is null)  then 1 else 0 end ) from projectSolution inner join projectQuestion on(projectSolution.repotGUID=projectQuestion.repotGUID) inner join project on (projectQuestion.projectGUID=project.projectGUID) inner join departments on (project.departWBS=departments.wbsCode) order by projectSolution.repotGUID DESC ";
                 return WMSqlExcuter.ExecuteDataTable(sql);

             }
             else
             {
                 DataTable dt = WMSqlExcuter.ExecuteDataTable(sql1, CommandType.Text, pars);
                 if (dt != null && dt.Rows.Count > 0)
                 { //属于行业线负责人
                     StringBuilder sb = new StringBuilder();
                     for (int i = 0; i < dt.Rows.Count; i++)
                     {
                         sb.Append("'");
                         sb.Append(dt.Rows[i].ItemArray[0]);
                         sb.Append("'");
                         if (i < dt.Rows.Count - 1)
                         {
                             sb.Append(",");
                         }
                     }
                     if (sb.Length == 0) { sb.Append("''"); }
                     string sql3 = "select qusetionDes, needAttention, advise, projectSolution.upfileGuid, projectSolution.repotGuid, questionState, questionType, userNo,expectSolveTime,CONVERT(VARCHAR(50),projectQuestion.projectGUID) as projectGUID, projectQuestion.cteateNo,createBy,projectManager, lastSales, sales,industryLineCode,departments.departName,project.departWBS,project.projectName,ifAlerm=(case when expectSolveTime<dateadd(d,-1,dateadd(d,1,convert(datetime,Convert(varchar(10),getDate(),120),120)))  and ((questionState<>'05' and questionState<>'04') or questionState is null)  then 1 else 0 end ) from projectSolution inner join projectQuestion on(projectSolution.repotGUID=projectQuestion.repotGUID) inner join project on (projectQuestion.projectGUID=project.projectGUID) inner join departments on (project.departWBS=departments.wbsCode) where (industryLineCode in (" + sb.ToString() + ") or projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo or projectQuestion.cteateNo=@userNo)  order by projectSolution.repotGUID DESC ";

                     return WMSqlExcuter.ExecuteDataTable(sql3, CommandType.Text, pars);
                 }
                 else
                 {
                     DataTable dt1 = WMSqlExcuter.ExecuteDataTable(sql2, CommandType.Text, pars);
                     if (dt1 != null && dt1.Rows.Count > 0)
                     {//属于部门负责人
                         StringBuilder sb1 = new StringBuilder();
                         for (int i = 0; i < dt1.Rows.Count; i++)
                         {
                             sb1.Append("'");
                             sb1.Append(dt1.Rows[i].ItemArray[1]);
                             sb1.Append("'");
                             if (i < dt1.Rows.Count - 1)
                             {
                                 sb1.Append(",");
                             }
                         }
                         if (sb1.Length == 0) { sb1.Append("''"); }
                         string sql4 = "select qusetionDes,needAttention,advise,projectSolution.upfileGuid,projectSolution.repotGuid,questionState,questionType,userNo,expectSolveTime,CONVERT(VARCHAR(50),projectQuestion.projectGUID) as projectGUID,projectQuestion.cteateNo,createBy,projectManager, lastSales, sales,industryLineCode,departments.departName,project.departWBS,project.projectName,ifAlerm=(case when expectSolveTime<dateadd(d,-1,dateadd(d,1,convert(datetime,Convert(varchar(10),getDate(),120),120)))  and ((questionState<>'05' and questionState<>'04') or questionState is null)  then 1 else 0 end ) from projectSolution inner join projectQuestion on(projectSolution.repotGUID=projectQuestion.repotGUID) inner join project on (projectQuestion.projectGUID=project.projectGUID) inner join departments on (project.departWBS=departments.wbsCode) where (departWBS in (" + sb1.ToString() + " ) or projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo or projectQuestion.cteateNo=@userNo) order by projectSolution.repotGUID DESC ";
                         return WMSqlExcuter.ExecuteDataTable(sql4, CommandType.Text, pars);
                     }
                     else
                     {//普通权限

                         string sql5 = "select qusetionDes,needAttention,advise,projectSolution.upfileGuid,projectSolution.repotGuid,questionState,questionType,userNo,expectSolveTime,CONVERT(VARCHAR(50),projectQuestion.projectGUID) as projectGUID,projectQuestion.cteateNo,createBy,projectManager, lastSales, sales,industryLineCode,departments.departName,project.departWBS,project.projectName,ifAlerm=(case when expectSolveTime<dateadd(d,-1,dateadd(d,1,convert(datetime,Convert(varchar(10),getDate(),120),120)))  and ((questionState<>'05' and questionState<>'04') or questionState is null)  then 1 else 0 end ) from projectSolution inner join projectQuestion on(projectSolution.repotGUID=projectQuestion.repotGUID) inner join project on (projectQuestion.projectGUID=project.projectGUID) inner join departments on (project.departWBS=departments.wbsCode) where  (projectManager=@userNo or lastSales=@userNo or sales=@userNo or createBy=@userNo or projectQuestion.cteateNo=@userNo)  order by projectSolution.repotGUID DESC ";

                         return WMSqlExcuter.ExecuteDataTable(sql5, CommandType.Text, pars);
                     }
                 }

             }
             
         }
    }
}
